Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


SQL Trace Functionality

Once SQL Trace is enabled, it gathers the following information:

  Parse, execute, and fetch counts. These counts can give you vital information about the efficiency of the SQL statements.
  CPU and elapsed times. This information can tell you which statements take the most time to execute.
  Physical and logical reads. This information can help you determine the effectiveness of the database buffer pool.
  Number of rows processed. This information can be used as an indication that more rows are being processed than you expected, thus indicating a problem.
  Library cache misses. This information can show you the effectiveness of the shared SQL area and how well you are reusing already parsed SQL statements.

SQL Trace puts this information into a trace file in an unreadable form. You then use the Oracle program TKPROF to format the trace information into useful, understandable data.

TKPROF Functionality

You use the Oracle program TKPROF to convert the SQL Trace information into data that is formatted to be understood by human beings. By specifying certain options, you can customize the output of TKPROF to some degree. TKPROF is invoked with the following syntax:

TKPROF inputfile outputfile [ Optional Parameters ]

In this syntax, inputfile is the data file generated by SQL Trace and outputfile is the name of the file to which you want the output of TKPROF to be written.

The optional parameters for KTPROF are as follows:


Parameter Description

EXPLAIN = username/password This option automatically runs the EXPLAIN PLAN command and adds the execution plan to the output of SQL Trace.
TABLE = schema.table This option specifies the schema and table name of the temporary table TKPROF uses when processing the SQL Trace data. If this option is not specified, TKPROF creates, uses, and then deletes a temporary table.
INSERT = scriptfile Creates a file of name scriptfile that contains the SQL statements TKPROF uses for storing trace file statistics.
SYS = [ YES/NO ] Determines whether SQL statements generated by user SYS or recursive SQL statements are listed.
PRINT = number Generates the output of only the first number of sorted SQL statements.
RECORD = recordfile Creates a file named recordfile that contains all recursive SQL statements.
SORT = sort_option This option sorts the output of SQL Trace in descending order based on the sort_option specified. The sort_option variable can be any of the following values:
PRSCNT Sorted by parse count
PRSCPU Sorted by CPU time spent parsing
PRSELA Sorted by elapsed time spent parsing
PRSDSK Sorted by number of physical reads from disk during parse
PRSQRY Sorted by number of consistent mode block reads during parse
PRSCU Sorted by number of current mode block reads during parse
EXECNT Sorted by number of executes
EXECPU Sorted by CPU time spent executing
EXEELA Sorted by elapsed time spent executing
EXEDSK Sorted by number of physical reads during execute
EXEQRY Sorted by number of consistent mode block reads during execute
EXECU Sorted by number of current mode block reads during execute
EXEROW Sorted by number of rows processed during execute
EXEMIS Sorted by number of library cache misses during execute
FCHCNT Sorted by number of fetches
FCHCPU Sorted by CPU time spent fetching
FCHELA Sorted by elapsed time spent fetching
FCHDSK Sorted by number of physical reads from disk during fetch
FCHQRY Sorted by number of consistent mode block reads during fetch
FCHCU Sorted by number of current mode block reads during fetch
FCHROW Sorted by number of rows fetched

With these options, SQL Trace can provide an abundance of data that can help you analyze your SQL statements. The following section examines some of the data SQL Trace provides.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.